﻿/******************************************************************************
'Description:装试生产进度看板
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:zhunan
'create Date:2018.09.13
 ********************************************************************************/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
namespace ReportBusiness
{
    public class ZSAEOSLookBoardBusiness
    {
        public uMESReportOracleDAL dal = new uMESReportOracleDAL();

        //查询QCDP已维护数据项 add zhunan 2020.4.17
        public DataSet GetQCDPItem(Dictionary<string, string> para)
        {
            string sql = @"
SELECT DISTINCT it.zscostitemname
FROM zscostitemyear it
WHERE it.itemtype = 'Q'
";
            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format(" AND it.factoryid = '{0}' ", para["factoryid"]);
            }
            if (para.ContainsKey("planyear"))
            {
                sql += string.Format(" AND it.planyear = '{0}' ", para["planyear"]);
            }

            DataTable dtQItems = dal.GetDataTable(sql);

            sql = @"
SELECT DISTINCT it.zscostitemname
FROM zscostitemyear it
WHERE it.itemtype = 'C'
";
            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format(" AND it.factoryid = '{0}' ", para["factoryid"]);
            }
            if (para.ContainsKey("planyear"))
            {
                sql += string.Format(" AND it.planyear = '{0}' ", para["planyear"]);
            }

            DataTable dtCItems = dal.GetDataTable(sql);

            sql = @"
SELECT DISTINCT it.zscostitemname
FROM zscostitemyear it
WHERE it.itemtype = 'D'
";
            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format(" AND it.factoryid = '{0}' ", para["factoryid"]);
            }
            if (para.ContainsKey("planyear"))
            {
                sql += string.Format(" AND it.planyear = '{0}' ", para["planyear"]);
            }

            DataTable dtDItems = dal.GetDataTable(sql);

            sql = @"
SELECT DISTINCT it.zscostitemname
FROM zscostitemyear it
WHERE it.itemtype = 'P'
";
            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format(" AND it.factoryid = '{0}' ", para["factoryid"]);
            }
            if (para.ContainsKey("planyear"))
            {
                sql += string.Format(" AND it.planyear = '{0}' ", para["planyear"]);
            }

            DataTable dtPItems = dal.GetDataTable(sql);

            DataSet dsResult = new DataSet();
            dsResult.Tables.Add(dtQItems);
            dsResult.Tables.Add(dtCItems);
            dsResult.Tables.Add(dtDItems);
            dsResult.Tables.Add(dtPItems);

            return dsResult;
        }

        //查询QCDP看板数据  add zhunan 2020.4.17
        public DataSet GetItemData(Dictionary<string, string> para)
        {
            string sql = @"
SELECT ti.usemonth || '月' usemonth,NVL(ti.planqty,0) planqty,NVL(ti.useqty,0) useqty,t.uomname
FROM zscostitemuseinfo ti 
LEFT JOIN zscostitemyear t ON ti.zscostitemyearid = t.zscostitemyearid
WHERE 1=1
";
            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format(" AND t.factoryid = '{0}' ", para["factoryid"]);   
            }
            if (para.ContainsKey("planyear"))
            {
                sql += string.Format(" AND t.planyear = '{0}' ", para["planyear"]);
            }
            if (para.ContainsKey("zscostitemname"))
            {
                sql += string.Format(" AND t.zscostitemname = '{0}' ", para["zscostitemname"]);
            }
            if (para.ContainsKey("itemtype"))
            {
                sql += string.Format(" AND t.itemtype = '{0}' ", para["itemtype"]);
            }
            sql += "ORDER BY ti.usemonth";

            DataTable dtResult = dal.GetDataTable(sql);

            sql = @"
SELECT SUM(ti.planqty) planqtySum,SUM(ti.useqty) useqtySum,t.total
FROM zscostitemuseinfo ti 
LEFT JOIN zscostitemyear t ON ti.zscostitemyearid = t.zscostitemyearid
WHERE 1=1
";
            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format(" AND t.factoryid = '{0}' ", para["factoryid"]);
            }
            if (para.ContainsKey("planyear"))
            {
                sql += string.Format(" AND t.planyear = '{0}' ", para["planyear"]);
            }
            if (para.ContainsKey("zscostitemname"))
            {
                sql += string.Format(" AND t.zscostitemname = '{0}' ", para["zscostitemname"]);
            }
            if (para.ContainsKey("itemtype"))
            {
                sql += string.Format(" AND t.itemtype = '{0}' ", para["itemtype"]);
            }

            sql += " GROUP BY t.total ";

            DataTable dtSum = dal.GetDataTable(sql);

            DataSet ds = new DataSet();
            ds.Tables.Add(dtResult);
            ds.Tables.Add(dtSum);

            return ds;
        }

        //查询QCDP指标每月明细信息 add zhunan 2020.4.17
        public DataTable GetItemDetails(Dictionary<string, string> para, out int pageCount, out int rowCount)
        {
            string sql = @"
SELECT t.zscostitemname,t.planyear || '年' planyear,ti.usemonth || '月' usemonth,
       ti.planqty || t.uomname planqty,ti.useqty || t.uomname useqty,ti.notes,t.total || t.uomname total
FROM zscostitemuseinfo ti 
LEFT JOIN zscostitemyear t ON ti.zscostitemyearid = t.zscostitemyearid
WHERE 1=1
";
            //分厂
            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format(" AND t.factoryid = '{0}' ", para["factoryid"]);
            }
            //年
            if (para.ContainsKey("planyear"))
            {
                sql += string.Format(" AND t.planyear = '{0}' ", para["planyear"]);
            }
            //月
            if (para.ContainsKey("month"))
            {
                sql += string.Format(" AND ti.usemonth = '{0}' ", para["month"]);
            }
            //指标项名称
            if (para.ContainsKey("zscostitemname"))
            {
                sql += string.Format(" AND t.zscostitemname = '{0}' ", para["zscostitemname"]);
            }
            //指标类型
            if (para.ContainsKey("itemtype"))
            {
                sql += string.Format(" AND t.itemtype = '{0}' ", para["itemtype"]);
            }

            uMESPagingDataDTO retR = dal.GetPagingDataIns(sql, int.Parse(para["page"].ToString()), int.Parse(para["rows"].ToString()));
            pageCount = Convert.ToInt32(retR.PageCount);
            rowCount = Convert.ToInt32(retR.RowCount);

            return retR.DBTable;
            
        }

        //查询S（安全）数据 add zhunan 2020.4.20
        public DataTable GetSafeData(Dictionary<string, string> para)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("type");
            dt.Columns.Add("total");
            dt.Columns.Add("finishedQty");

            DataRow dr1 = dt.NewRow();
            dr1["type"] = "S指标任务";
            dr1["total"] = 0;
            dr1["finishedQty"] = 0;
            dt.Rows.Add(dr1);

            DataRow dr2 = dt.NewRow();
            dr2["type"] = "S指标问题";
            dr2["total"] = 0;
            dr2["finishedQty"] = 0;
            dt.Rows.Add(dr2);

            //S指标任务
            string sql = @"
SELECT t.factoryid,NVL(COUNT(t.operationcontent),0) total,NVL(SUM(CASE WHEN t.status = '已达标' THEN 1 ELSE 0 END),0) finishedQty
FROM
(
    SELECT sv.factoryid,m.operationcontent,mt.completedate,m.localtime,decode(mt.status,1,'已达标','未达标') status
    FROM manageroperationflowcomplete mt
    LEFT JOIN manageroperationflow m ON m.manageroperationflowid = mt.manageroperationflowid
    LEFT JOIN sessionvalues sv ON sv.employeeid = m.createemployeeid
    LEFT JOIN factory f ON f.factoryid = sv.factoryid
    WHERE trunc(mt.completedate) >= TRUNC(SYSDATE,'yyyy') AND trunc(mt.completedate) <= add_months(trunc(sysdate, 'yyyy'), 12) - 1
          AND m.sqcdptype = 0
";
            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format(" AND sv.factoryid = '{0}'",para["factoryid"]);
            }

            sql += " ) t GROUP BY t.factoryid";

            DataTable dtSafeIndexData = dal.GetDataTable(sql);

            sql = @"
SELECT up.factoryid,NVL(COUNT(up.ufcproblemid),0) total,NVL(SUM(CASE WHEN up.ufcstatus = '0' THEN 1 ELSE 0 END),0) finishedQty
FROM ufcproblem up
WHERE up.ufccontent='S问题' AND TRUNC(up.ufccreatedate) >= TRUNC(SYSDATE,'yyyy') AND TRUNC(up.ufccreatedate) <= add_months(TRUNC(sysdate, 'yyyy'), 12) - 1
";
            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format(" AND up.factoryid = '{0}'", para["factoryid"]);
            }

            sql += " GROUP BY up.factoryid";

            DataTable dtProblem = dal.GetDataTable(sql);

            if (dtSafeIndexData.Rows.Count > 0)
            {
                dt.Rows[0]["total"] = dtSafeIndexData.Rows[0]["total"];
                dt.Rows[0]["finishedQty"] = dtSafeIndexData.Rows[0]["finishedQty"];
            }

            if (dtProblem.Rows.Count > 0)
            {
                dt.Rows[1]["total"] = dtProblem.Rows[0]["total"];
                dt.Rows[1]["finishedQty"] = dtProblem.Rows[0]["finishedQty"];
            }

            return dt;
        }

        //查询S（安全）项明细 add zhunan 2020.4.20
        public DataTable GetSafeItemDetails(Dictionary<string, string> para, out int pageCount, out int rowCount)
        {
            string sql = @"
SELECT m.operationcontent,mt.completedate createdate,m.localtime,
        CASE WHEN m.cycle = 0 THEN '每天' || m.localtime
             WHEN m.cycle = 1 THEN '每天' || m.timenum || '次'
             WHEN m.cycle = 2 THEN '每周' || m.timenum || '次'
             WHEN m.cycle = 3 THEN '每月' || m.timenum || '次'
             WHEN m.cycle = 4 THEN '每月' || m.timenum || '日' END cycle,
        CASE WHEN mt.status = 1 THEN '1次' ELSE '0次' END dealcount,
        DECODE(mt.status,1,'已达标','未达标') status
FROM manageroperationflowcomplete mt
LEFT JOIN manageroperationflow m ON m.manageroperationflowid = mt.manageroperationflowid
LEFT JOIN sessionvalues sv ON sv.employeeid = m.createemployeeid
LEFT JOIN factory f ON f.factoryid = sv.factoryid
WHERE trunc(mt.completedate) >= TRUNC(SYSDATE,'yyyy') AND trunc(mt.completedate) <= add_months(trunc(sysdate, 'yyyy'), 12) - 1 AND m.sqcdptype = 0 
";
            //分厂
            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format(" AND sv.factoryid = '{0}' ", para["factoryid"]);
            }

            //指标类型
            if (para.ContainsKey("filter"))
            {
                if (para["filter"] == "finishedQty")
                {
                    sql += "AND mt.status = 1 ";
                }
            }

            sql += " ORDER BY mt.completedate";

            uMESPagingDataDTO retR = dal.GetPagingDataIns(sql, int.Parse(para["page"].ToString()), int.Parse(para["rows"].ToString()));
            pageCount = Convert.ToInt32(retR.PageCount);
            rowCount = Convert.ToInt32(retR.RowCount);

            return retR.DBTable;

        }

        //查询S指标问题上报明细 add zhunan 2020.4.20
        public DataTable GetSafeProblemDetails(Dictionary<string, string> para, out int pageCount, out int rowCount)
        {
            string sql = @"
SELECT up.description,w.workcentername,e.fullname,up.ufcchulidate,ce.fullname createperson,up.ufccreatedate,up.ufcresult,
        DECODE(up.ufcwtdj,'3','一般（3天）',2,'急（1天）',1,'紧急（4H）',0,'特急（1H）') priority,
        CASE WHEN up.ufcstatus=0 THEN '已完成' WHEN up.ufcstatus=1 THEN '未处理' WHEN up.ufcstatus=2 THEN '处理中' WHEN up.ufcstatus=3 THEN '处理完' END AS ufcstatus,
        CASE WHEN ROUND((SYSDATE-up.ufcchulidate),2) < 0 THEN 0 ELSE  ROUND((SYSDATE-up.ufcchulidate),2) END  AS delayday
FROM ufcproblem up
LEFT JOIN Workcenter w ON w.workcenterid = up.workcenterid
LEFT JOIN employee e ON e.employeeid = up.ufctoemployeeid
LEFT JOIN employee ce ON ce.employeeid = up.ufccreateemployeeid
LEFT JOIN sessionvalues sv ON sv.employeeid = ce.employeeid
LEFT JOIN workcenter cw ON cw.workcenterid = sv.workcenterid
WHERE up.ufccontent='S问题'
";
            //分厂
            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format(" AND up.factoryid = '{0}' ", para["factoryid"]);
            }

            //指标类型
            if (para.ContainsKey("filter"))
            {
                if (para["filter"] == "finishedQty")
                {
                    sql += "AND up.ufcstatus = '0' ";
                }
            }

            uMESPagingDataDTO retR = dal.GetPagingDataIns(sql, int.Parse(para["page"].ToString()), int.Parse(para["rows"].ToString()));
            pageCount = Convert.ToInt32(retR.PageCount);
            rowCount = Convert.ToInt32(retR.RowCount);

            return retR.DBTable;

        }
    }
}
